﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.IO;
using System.Data.OleDb;

namespace Hotel.Vendor.HotelStaticInfoManagement.BookingHotelFilter
{
    public class FileManagement
    {
        private static string CSVFileFolder = ConfigurationManager.AppSettings["CSVFileFolder"];
        private static string TempFileDirectory = ConfigurationManager.AppSettings["TempFileDirectory"];
                
        /// <summary>
        /// 取得临时文件 文件名
        /// </summary>
        /// <param name="rootPath">网站根目录绝对地址</param>
        /// <param name="fileFullName">文件名</param>
        /// <returns>临时文件路径</returns>
        public static string GetCSVFileName(string rootPath, string fileFullName)
        {
            string fileName = Path.GetFileNameWithoutExtension(fileFullName);
            string fileExt = Path.GetExtension(fileFullName);

            DateTime dateNow = DateTime.Now;
            string date = dateNow.ToString("yyyyMMdd");
            string time = dateNow.ToString("HHmmss");

            string tmpDirectoryName = CSVFileFolder;
            string tmpDirectoryPath = rootPath + @"\" + tmpDirectoryName + @"\" + date;

            if (!Directory.Exists(tmpDirectoryPath))
            {
                Directory.CreateDirectory(tmpDirectoryPath);
            }

            return tmpDirectoryPath + @"\" + fileName + time + fileExt;
        }


        /// <summary>
        /// 生成csv文件
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="table">数据</param>
        public static void DataTableToCSV(string filePath, DataTable table)
        //public static string DataTableToCSV(string rootPath, string fileName,DataTable table)
        {
            //string tmpFilePath = GetTempFileName(rootPath, fileName);

            string title = "";
            FileStream fs = new FileStream(filePath, FileMode.Create);
            StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.UTF8);
            for (int i = 0; i < table.Columns.Count; i++)
            {
                title += FormatCsvContent(table.Columns[i].ColumnName) + ",";
            }
            title = title.Substring(0, title.Length - 1) + "\n";
            sw.Write(title);
            foreach (DataRow row in table.Rows)
            {
                string line = "";
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    line += FormatCsvContent(row[i].ToString()) + ",";
                }
                line = line.Substring(0, line.Length - 1) + "\n";
                sw.Write(line);
            }
            sw.Close();
            fs.Close();

            //return filePath;
        }

        /// <summary>
        /// CSV字段中含双引号 逗号时处理
        /// </summary>
        /// <param name="content">处理内容</param>
        /// <returns></returns>
        private static string FormatCsvContent(string content)
        {
            if (string.IsNullOrEmpty(content)) return content;

            if (content.Contains("\""))//字段中存在双引号
            {
                content = content.Replace("\"", "\"\"");
            }

            content = "\"" + content + "\"";


            return content;
        }

        /// <summary>
        /// 判断文件是否为支持的 Csv文件
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="fileExt">文件扩展名</param>
        /// <returns>true 支持 FALSE 不支持</returns>
        public static bool IsSuportFileExtension(string fileName, ref string fileExt)
        {
            try
            {
                fileExt = System.IO.Path.GetExtension(fileName);

                switch (fileExt.ToLower())
                {
                    case ".xls":
                        return true;
                    case ".xlsx":
                        return true;
                    case ".csv":
                        return true;
                    default:
                        return false;
                }
            }
            catch
            {
                fileExt = "";
                return false;
            }
        }

        ///// <summary>
        ///// 将上传的文件保存至临时文件。
        ///// 当上传文件导入时使用本方法。
        ///// </summary>
        ///// <param name="uploadControl">上传文件控件。</param>
        ///// <returns>保存至临时文件的完全物理路径。</returns>
        //public static string SaveAsTempFile(string rootPath, FileUpload uploadControl)
        //{
        //    string fileName = Path.GetFileName(uploadControl.FileName);

        //    string tmpFilePath = GetTempFileName(rootPath, fileName);
        //    uploadControl.SaveAs(tmpFilePath);
        //    return tmpFilePath;
        //}

        /// <summary>
        /// 取得临时文件 文件名
        /// </summary>
        /// <param name="rootPath">网站根目录绝对地址</param>
        /// <param name="fileFullName">文件名</param>
        /// <returns>临时文件路径</returns>
        public static string GetTempFileName(string rootPath, string fileFullName)
        {
            string fileName = Path.GetFileNameWithoutExtension(fileFullName);
            string fileExt = Path.GetExtension(fileFullName);

            DateTime dateNow = DateTime.Now;
            string date = dateNow.ToString("yyyyMMdd");
            string time = dateNow.ToString("HHmmss");

            string tmpDirectoryName = TempFileDirectory;
            string tmpDirectoryPath = rootPath + @"\" + tmpDirectoryName + @"\" + date;

            if (!Directory.Exists(tmpDirectoryPath))
            {
                Directory.CreateDirectory(tmpDirectoryPath);
            }

            return tmpDirectoryPath + @"\" + fileName + time + fileExt;
        }

        /// <summary>
        /// 从指定的Excel文件导入
        /// </summary>
        /// <param name="strFileName">Excel文件名</param>
        /// <returns></returns>
        public static DataTable ImportFromExcel(string strFileName, string SheetName)
        {
            DataTable dt = new DataTable();
            dt = doImportExcel(strFileName, SheetName);
            return dt;
        }

        /// <summary>
        /// 执行导入
        /// </summary>
        /// <param name="strFileName">文件名</param>
        /// <returns>DataSet</returns>
        private static DataTable doImportExcel(string strFileName, string SheetName)
        {
            string strConn;

            if (strFileName == "") return null;

            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" +
              "Data Source=" + strFileName + ";" +
              "Extended Properties=Excel 8.0;";
            OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [" + SheetName + "$]", strConn);

            DataTable dtExcel = new DataTable();

            ExcelDA.Fill(dtExcel);

            return dtExcel;
        }

        public static DataTable ImportFromCSV(string strFileName)
        {
            DataTable dt = new DataTable();
            dt = doImportCSV(strFileName, strFileName.Split('\\')[strFileName.Split('\\').Length - 1]);
            return dt;
        }

        public static DataTable ImportFromCSV(string strFileName, string[] fields)
        {
            DataTable dt = new DataTable();
            dt = doImportCSV(strFileName, strFileName.Split('\\')[strFileName.Split('\\').Length - 1], fields);
            return dt;
        }

        /// <summary>
        /// 执行导入
        /// </summary>
        /// <param name="strFileName">文件名</param>
        /// <returns>DataSet</returns>
        private static DataTable doImportCSV(string strFileName, string SheetName)
        {
            string strConn;

            if (strFileName == "") return null;

            strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Text;HDR=Yes;FMT=Delimited';DATA SOURCE=" + strFileName.Replace("\\" + SheetName, "");

            OleDbDataAdapter ExcelDA = new OleDbDataAdapter("select * from [" + SheetName + "]", strConn);

            DataTable dtCSV = new DataTable();

            ExcelDA.Fill(dtCSV);

            return dtCSV;
        }

        private static DataTable doImportCSV(string strFileName, string SheetName, string[] fields)
        {
            string strConn;

            if (strFileName == "") return null;

            strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Text;DATA SOURCE=" + strFileName.Replace("\\" + SheetName, "");

            OleDbDataAdapter ExcelDA = new OleDbDataAdapter("select * from " + SheetName, strConn);

            DataTable dtCSV = new DataTable();

            foreach (string field in fields)
            {
                dtCSV.Columns.Add(field, typeof(string));
            }

            ExcelDA.Fill(dtCSV);

            return dtCSV;
        }       
    }
}